MySQL 高级 - 4

普通索引和唯一索引，应该怎么选择？

查询过程

普通索引跟唯一索引执行上的区别：普通索引的等值查询，会继续遍历到第一个不相等的值才会结束，而唯一索引等值查询，命中则结束。InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算，所以普通索引和唯一索引的性能差别微乎其微。

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：
	对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
	对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。两者几乎没有性能差别
	
第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：
	对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
	对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 使用场景：

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

所以唯一索引和普通索引这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你因此如果业务可以接受，尽量选择普通索引。

MySQL 选错索引？

MySQL 为啥会选错索引：优化器认为使用那个索引检索数据的速度比较快是一个需要各种因素综合评估的事情，比如：是否使用临时表、是否排序、扫描的行数多少、回表的次数等

MySQL 选错索引怎么办：

强制指定使用某个索引，不常用不推荐用
调整 SQL 语句，使优化器选择的和我们想的一样，不具有通用性
调整SQL语句，使优化器选择的和我们想的一样，不具有通用性
使用analyze table可以解决索引统计信息不准确导致的索引选错的问题

什么时候合适使用前缀索引？

假设，你现在维护一个支持邮箱登录的系统，用户表是这么定义的：

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
... 
)engine=innodb;

创建两个索引

1
2
3

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

第一个语句创建的 index1 索引里面，包含了每个记录的整个字符串；而第二个语句创建的 index2 索引里面，对于每个记录都是只取前 6 个字节。

直接创建完整索引，这样可能比较占用空间，例如上面 index1 比 index2 占用更多空间，占用的磁盘空间越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；（因为前缀索引不知道是否完全匹配了，所以一定会回表查询，所以就不能使用覆盖索引了）

如果是对身份证之类的就行存储，那么还可以使用倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

如果是对身份证之类的就行存储，还可以创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，但是跟上种方式一样，都不支持范围扫描。

SQL 查询突然变慢了一下

脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

什么情况会引发数据库的 flush：

InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
MySQL 认为系统“空闲”的时候
MySQL 正常关闭的时候

前两者引发的刷脏页的操作可能引发性能问题

为什么删掉表数据一半，表文件大小不变？

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。（MySQL 5.6.6 版本开始，它的默认值就是 ON）

建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

数据的删除，插入都会造成数据的空洞，使用 alter table A engine=InnoDB 重建表空间，释放空洞的空间，缩容。 MySQL 5.6 之前要求在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。

MySQL 5.6 版本开始引入的 Online DDL，表 A 中可以有更新，实现原理是记录一个 row log，存放的是 A 的后续操作记录，最后在恢复。